Use master
go

IF EXISTS (SELECT * FROM sys.databases WHERE name='HOTELBOOKING')
	DROP DATABASE HOTELBOOKING
go

CREATE DATABASE HOTELBOOKING
go

use HOTELBOOKING
go


CREATE TABLE [User] (
	UserName nvarchar(100) primary key,
	PassWord nvarchar(100) not null,
	FristName nvarchar(100),
	LastName nvarchar(100),
	Sex char(1),
	DateOfBrith datetime,
	Address nvarchar(255),
	Email nvarchar(100),
	PhoneNumber nvarchar(20),
	Genre char(1),
	MaxContractedHotel int,
	Fax nvarchar(255),
	IsBlocked bit,
	Point int
)
Go

CREATE TABLE [Order](
	OrderID uniqueidentifier primary key,
	CreatedDate datetime,
	CheckInDate datetime,
	CheckOutDate datetime,
	TotalPrice float,
	Status nvarchar(20),
	CustomerName nvarchar(100),
	CustomerAddress nvarchar(255),
	CustomerEmail nvarchar(100),
	CustomerPhone nvarchar(20),
	SpecialRequests nvarchar(max),
	TypePayment int identity,
	UserName nvarchar(100)
)

 CREATE TABLE OrderDetail(
	OrderID uniqueidentifier,
	RoomID int,
	Quantity int,
	UnitCost float,
	Subtotal  AS (Quantity*UnitCost)
	primary key(OrderID, RoomID)
 )
 
 CREATE TABLE TypeOfPayMent(
	PaymentID int identity primary key,
	PaymentName nvarchar(255)
)

 CREATE TABLE StaffInHotel(
	UserName nvarchar(100),
	HotelID int,
	IsBlocked bit,
	CanEditHotel bit,
	CanAddRoomType bit,
	CanEditRoomType bit,
	CanDeleteRoomType bit,
	CanAddRoom bit,
	CanEditRoom bit,
	CanDeleteRoom bit,
	CanUpdateOrder bit,
	Primary key (HotelID, UserName)
)

CREATE TABLE Rate(
	HotelID int,
	UserName nvarchar(100),
	Point int,
	Primary key (HotelID, UserName)
)

CREATE TABLE Hotel(
	ID int identity(1,1) primary key,
	Proprietor nvarchar(100),
	CityID int,
	Name nvarchar(100),
	OtherName nvarchar(100),
	PhoneNumber nvarchar(20),
	[Address] nvarchar(255),
	Price float,
	Star int,
	[Description] nvarchar(MAX),
	[Image] nvarchar(255),
	Thumbnail nvarchar(255),
);
 
 CREATE TABLE PaymentOfHotel(
	PaymentID int,
	HotelID int,
	Primary key (PaymentID,HotelID)
);

CREATE TABLE Mark (
	HotelID int, 
	UserName nvarchar(100),
	Primary key (HotelID, UserName),
);
CREATE TABLE Hotel_HFacility(
	HotelID int,
	HotelFacilityID int,
	Primary key (HotelID, HotelFacilityID),
);

CREATE TABLE HotelFacility(
	ID int identity(1,1) primary key,
	Facility nvarchar(100),
);
 
CREATE TABLE City(
	ID int identity(1,1) primary key,
	Name nvarchar(100),
	OtherName nvarchar(100),
	[Description] nvarchar(MAX),
	[Image] nvarchar(255),
	Thumbnail nvarchar(255),
); 

CREATE TABLE RoomType(
	ID int identity(1,1) primary key,
	HotelID int,
	Name nvarchar(100),
	MaxOfPeople int,
	Area float,
	Price float,
	[Description] nvarchar(MAX),
	[Image] nvarchar(255),
);

CREATE TABLE RType_RFacility(
	RoomTypeID int,
	RoomFacilityID int, 
	Primary key (RoomTypeID, RoomFacilityID),
);
CREATE TABLE RoomFacility(
	ID int identity(1,1) primary key,
	Facility nvarchar(100),
);

CREATE TABLE RType_Promotion(
	RoomTypeID int,
	PromotionID int,
	PromotionPrice float,
	Primary key (RoomTypeID, PromotionID), 
);

CREATE TABLE Promotion(
	ID int identity(1,1) primary key,
	StartedDate datetime,
	UntilDate datetime,
	Reason nvarchar(MAX),
);

CREATE TABLE ValidRoom(
	ID int identity(1,1) primary key,
	RoomID int,
	StartedDate datetime,
	UntilDate datetime,
	Reason nvarchar(500),
);

CREATE TABLE Room(
	ID int identity(1,1) primary key,
	RoomTypeID int,
	Name nvarchar(100),
);

CREATE TABLE Review(
	ID int identity(1,1) primary key,
	HotelID int,
	UserName nvarchar(100),
	CreatedDate datetime,
	ReviewContent nvarchar(500),
	IsReportSpam bit,
);
--
-- Foreign key Hotel
--
ALTER TABLE Hotel ADD CONSTRAINT FK_Hotel_City
	FOREIGN KEY (ID) references City(ID)
ALTER TABLE Hotel ADD CONSTRAINT FK_Hotel_USER
	FOREIGN KEY (Proprietor) references [USER](UserName)
	
--
-- Foreign key Hotel_HFacility
--
ALTER TABLE Hotel_HFacility ADD CONSTRAINT FK_Hotel_HFacility_Hotel
	FOREIGN KEY (HotelID) references Hotel(ID)
ALTER TABLE Hotel_HFacility ADD CONSTRAINT FK_Hotel_HFacility_HotelFacility
	FOREIGN KEY (HotelFacilityID) references HotelFacility(ID)

--
-- Foreign key Reivew
--
ALTER TABLE Review ADD CONSTRAINT FK_Review_Hotel
	FOREIGN KEY (HotelID) references Hotel(ID)
ALTER TABLE Review ADD CONSTRAINT FK_Review_User
	FOREIGN KEY (UserName) references [User](UserName)

--
-- Foreign key Room
--
ALTER TABLE Room ADD CONSTRAINT FK_Room_RoomTpye
	FOREIGN KEY (RoomTypeID) references RoomType(ID)

--
-- Foreign key ValidRoom
--
--
ALTER TABLE ValidRoom ADD CONSTRAINT FK_ValidRoom_Room
	FOREIGN KEY (RoomID) references Room(ID)
	
--
-- Foreign key RoomType
--
ALTER TABLE RoomType ADD CONSTRAINT FK_RoomType_Hotel
	FOREIGN KEY (HotelID) references Hotel(ID)

--
-- Foreign key RType_RFacility
--
ALTER TABLE RType_RFacility ADD CONSTRAINT FK_RType_RFacility_RoomType
	FOREIGN KEY (RoomTypeID) references RoomType(ID)
ALTER TABLE RType_RFacility ADD CONSTRAINT FK_RType_RFacility_RoomFacility
	FOREIGN KEY (RoomFacilityID) references RoomFacility(ID)

--
-- Foreign key RType_Promotion
--
ALTER TABLE RType_Promotion ADD CONSTRAINT FK_RType_Promotion_RoomType
	FOREIGN KEY (RoomTypeID) references RoomType(ID)
ALTER TABLE RType_Promotion ADD CONSTRAINT FK_RType_Promotion_Promotion
	FOREIGN KEY (PromotionID) references Promotion(ID)

--
-- Foreign key Mark
--
ALTER TABLE Mark ADD CONSTRAINT FK_Mark_Hotel
	FOREIGN KEY (HotelID) references Hotel(ID)
ALTER TABLE Mark ADD CONSTRAINT FK_Mark_Users
	FOREIGN KEY (UserName) references [User](UserName)
	
--
-- Foreign key Rate
--
ALTER TABLE Rate ADD CONSTRAINT FK_Rate_Hotel
	FOREIGN KEY (HotelID) references Hotel(ID)
ALTER TABLE Rate ADD CONSTRAINT FK_Rate_Users
	FOREIGN KEY (UserName) references [User](UserName)

--
-- Foreign key StaffInHotel
--
ALTER TABLE StaffInHotel ADD CONSTRAINT FK_StaffInHotel_Hotel
	FOREIGN KEY (HotelID) references Hotel(ID)
ALTER TABLE StaffInHotel ADD CONSTRAINT FK_StaffInHotel_Users
	FOREIGN KEY (UserName) references [User](UserName)
--
-- Foreign key User
--
--
-- Foreign key TypeOfPayment
--
--
-- Foreign key Order
--
ALTER TABLE [Order] ADD CONSTRAINT FK_Order_TypeOfPayment
	FOREIGN KEY (TypePayment) references TypeOfPayment(PaymentID)
ALTER TABLE [Order] ADD CONSTRAINT FK_Order_Users
	FOREIGN KEY (UserName) references [User](UserName)
--
-- Foreign key OrderDetail
--
ALTER TABLE OrderDetail ADD CONSTRAINT FK_OrderDetail_Room
	FOREIGN KEY (RoomID) references Room(ID)
ALTER TABLE OrderDetail ADD CONSTRAINT FK_OrderDetail_Order
	FOREIGN KEY (OrderID) references [Order](OrderID)

--
-- Foreign key PaymentOfHotel
--
ALTER TABLE PaymentOfHotel ADD CONSTRAINT FK_PaymentOfHotel_Hotel
	FOREIGN KEY (HotelID) references Hotel(ID)
ALTER TABLE PaymentOfHotel ADD CONSTRAINT FK_PaymentOfHotel_TypeOfPayment
	FOREIGN KEY (PaymentID) references TypeOfPayment(PaymentID)